﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CCodeTreeLib;
using SqlOrm.Xml;
using System.Configuration;
using System.Data;
using SqlOrm.ScriptBuilder;
using SqlOrm.ADO;


namespace SqlOrm.TreeBuilders
{
    public class SqlContextTreeBuilder : CCodeTree
    {
        DataDictionary _ddl;
        IADO _ado;

        public SqlContextTreeBuilder(DataDictionary ddl)
        {
            _ddl = ddl;
            //Procedural = true;
            _ado = ADOFactory.GetADO(ddl);
            Build();
        }
        public bool Procedural
        {
            get
            {
                if (_ddl.Settings.Database.ver == DatabaseVersion.SqlCompact35)
                    return false;
                return _ddl.Settings.ScriptOutput != null; 
            }
        }

        public void Build()
        {
            BuildContext();
        }

        private void BuildContext()
        {
            Console.WriteLine("Building Context");
            Comments.Add("This code was generated by some tool");
            Comments.Add("SqlData.exe");
            var ns = AddStatement(new CCodeNamespace { Name = _ddl.Settings.ContextOutput.clrnamespace });
            var context = new CCodeClass(_ddl.Settings.Database.name + "Context");
            context.Modifier = Modifier.Public;
            context.ImplementedInterfaces.Add("IDisposable");
            context.Comments.Add("DAL implementation.");
            ns.AddStatement(context);

            Usings.Add(new CCodeUsing("System"));
            Usings.Add(new CCodeUsing("System.Collections.Generic"));
            Usings.Add(new CCodeUsing("System.Data"));
            Usings.Add(new CCodeUsing(_ado.GetUsing()));
            Usings.Add(new CCodeUsing("System.Linq"));
            if (!Procedural)
                Usings.Add(new CCodeUsing("System.Text"));
            if (_ddl.Settings.ContextOutput.clrnamespace != _ddl.Settings.EntitiesOutput.clrnamespace)
                Usings.Add(new CCodeUsing(_ddl.Settings.EntitiesOutput.clrnamespace));

            context.AddMember(new CCodeField(typeof(object), "_syncRoot") { IsReadOnly = true, Initializer = new CCodeSnippet("new object()") });
            context.AddMember(new CCodeField(_ado.GetConnectionType, "_conn"));
            context.AddMember(new CCodeField(_ado.GetTransactionType, "_transaction"));
            context.AddMember(new CCodeField("bool", "_transactionActive"));
            var kaf = new CCodeField("bool", "_keepAlive");
            context.AddMember(kaf);

            var dc = context.AddMember(new CCodeConstructor(context, new CCodeField("string", "connectionString")));
            dc.AddStatement(new CCodeSnippet("_conn = new {0}(connectionString);".F(_ado.GetConnectionType)));


            context.AddMember(new CCodeProperty("KeepAlive", "bool") { Field = kaf });

            BuildExecuteSqlMethod(context);
            BuildDisposeMethod(context);
            BuildTransactions(context);

            BuildAlls(context);
            BuildDeletes(context);
            BuildGets(context);
            BuildInserts(context);
            BuildSearches(context);
            BuildUpserts(context);
            BuildUpdates(context);
            BuildProcs(context);

            var i = ns.AddInterface(new CCodeInterface("I" + _ddl.Settings.Database.name + "Context"));
            i.Modifier = Modifier.Public;
            Array.ForEach(context.OfType<CCodeMethod>().ToArray(), x => i.AddStatement(x));
            Array.ForEach(context.OfType<CCodeProperty>().ToArray(), x => i.AddStatement(x));

            context.ImplementedInterfaces.Add(i.Name);
        }

        private void BuildDeletes(CCodeClass context)
        {
            Console.WriteLine("Building Delete methods");
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => x.delete && x.Field.Any(f => f.key)))
            {
                var dm = context.AddMember(new CCodeMethod("void", string.Format("Delete{0}", table.ClassName)));
                foreach (var f in table.Field.Where(x => x.key))
                    dm.Parameters.Add(new CCodeField(f.ClrDataType, f.ParamName));
                var l = new CCodeLockStatment("_syncRoot");
                dm.AddStatement(l);
                MaybeOpenConnection(l);

                string commandText = DeleteCommandText(table);
                var uc = l.AddStatement(new CCodeUsingStatement(string.Format("var command = new {0}(\"{1}\", _conn)", _ado.GetCommandType, commandText)));
                if (_ddl.Settings.CommandTimeoutSpecified)
                    uc.AddStatement(new CCodeSnippet("command.CommandTimeout = " + _ddl.Settings.CommandTimeout + ";"));
                if (Procedural)
                    uc.AddStatement(new CCodeSnippet(string.Format("command.CommandType = CommandType.StoredProcedure;")));
                uc.AddStatement(new CCodeIfStatement("_transactionActive", new CCodeSnippet("command.Transaction = _transaction;")));

                //if (Procedural)
                //SqlParameters(table.Field.Where(x => x.key).ToList(), true, uc);
                SqlParamsWithValue(table.Field.Where(x => x.key), uc);

                uc.AddStatement(new CCodeSnippet("command.ExecuteNonQuery();"));
                MaybeCloseConnection(l);
            }
        }

        private string DeleteCommandText(TableDefinition table)
        {
            if (Procedural)
                return "Delete" + table.ClassName;
            var result = new StringBuilder();
            result.Append(string.Format("delete from {0} where ", table.FullyQualifiedSqlName));
            result.Append(table.Field.Where(x=>x.key).SepList(f=> "[" + f.name + "] = @" + f.ParamName,  f=> " and "));
            return result.ToString();
        }

        private void BuildUpserts(CCodeClass context)
        {
            Console.WriteLine("Building Upsert methods");
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => x.upsert && x.Field.Any(f => f.key)))
            {
                var um = context.AddMember(new CCodeMethod("void", string.Format("Upsert{0}", table.ClassName)));
                um.Parameters.Add(new CCodeField(table.ClassName, "det"));
                var l = um.AddStatement(new CCodeLockStatment("_syncRoot"));

                MaybeOpenConnection(l);

                string query = UpsertCommandText(table);
                l.AddStatement("string query = @\"" + query + "\";");
                var uc = l.AddStatement(new CCodeUsingStatement(string.Format("var command = new {0}(query, _conn)".F(_ado.GetCommandType))));
                if (_ddl.Settings.CommandTimeoutSpecified)
                    uc.AddStatement(new CCodeSnippet("command.CommandTimeout = " + _ddl.Settings.CommandTimeout + ";"));
                if (Procedural)
                    uc.AddStatement(new CCodeSnippet(string.Format("command.CommandType = CommandType.StoredProcedure;")));
                uc.AddStatement(new CCodeIfStatement("_transactionActive", new CCodeSnippet("command.Transaction = _transaction;")));

                //SqlParameters(table.Field.ToList(), false, uc);
                SqlParamsWithValue(table.Field, uc, true);

                uc.AddStatement(new CCodeSnippet("command.ExecuteNonQuery();"));
                MaybeCloseConnection(l);

            }
        }

        private string UpsertCommandText(TableDefinition table)
        {
            if (Procedural)
                return "Upsert" + table.ClassName;
            var result = new StringBuilder();

            result.Append(string.Format("if exists(select * from {0} where ", table.FullyQualifiedSqlName));
            result.AppendLine(table.Field.Where(x => x.key).SepList(f => "[" + f.name + "] = @" + f.name , f => " and ") + ") begin");
            result.AppendLine(string.Format("\tupdate {0} set " + table.Field.Where(x => !x.key).SepList(f => "[" + f.name + "] = @" + f.name, f => ", ") + " where " + table.Field.Where(x => x.key).SepList(f => "[" + f.name + "] = @" + f.name, f => " and ") + ")", table.FullyQualifiedSqlName));
            result.AppendLine("end");
            result.AppendLine("else begin");
            result.AppendLine(string.Format("\tinsert into {0} (" + table.Field.Where(x => !x.identity).SepList(f => "[" + f.name + "]", f => ", ") + ") values (" + table.Field.Where(x => !x.identity).SepList(f => "@" + f.name, f => ", ") + ")", table.FullyQualifiedSqlName));
            result.Append("end");
            return result.ToString();
        }

        private void BuildUpdates(CCodeClass context)
        {
            Console.WriteLine("Building Update methods");
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => x.update && x.Field.Any(f => f.key)))
            {
                var um = context.AddMember(new CCodeMethod("void", string.Format("Update{0}", table.ClassName)));
                um.Parameters.Add(new CCodeField(table.ClassName, "det"));
                foreach (var sf in table.Field.Where(x => x.specialSpecified))
                {
                    if (sf.special == SpecialField.updatedDate)
                        um.AddStatement("det.{0} = DateTime.Now;".F(sf.title));
                }
                var l = um.AddStatement(new CCodeLockStatment("_syncRoot"));

                MaybeOpenConnection(l);

                string query = UpdateCommandText(table);
                l.AddStatement("string query = @\"" + query + "\";");
                var uc = l.AddStatement(new CCodeUsingStatement(string.Format("var command = new {0}(query, _conn)".F(_ado.GetCommandType))));
                if (_ddl.Settings.CommandTimeoutSpecified)
                    uc.AddStatement(new CCodeSnippet("command.CommandTimeout = " + _ddl.Settings.CommandTimeout + ";"));
                if (Procedural)
                    uc.AddStatement(new CCodeSnippet(string.Format("command.CommandType = CommandType.StoredProcedure;")));
                uc.AddStatement(new CCodeIfStatement("_transactionActive", new CCodeSnippet("command.Transaction = _transaction;")));

                //SqlParameters(table.Field.ToList(), false, uc);
                SqlParamsWithValue(table.Field, uc, true);

                uc.AddStatement(new CCodeSnippet("command.ExecuteNonQuery();"));
                MaybeCloseConnection(l);
            }
        }

        private string UpdateCommandText(TableDefinition table)
        {
            var fields = table.Field.Where(x=>!x.specialSpecified || (x.specialSpecified && x.special != SpecialField.createdDate ));
            if (Procedural)
                return "Update" + table.ClassName;
            var result = new StringBuilder();
            result.Append(string.Format("update {0} set " + fields.Where(x => !x.key).SepList(f => "[" + f.name + "] = @" + f.name, f => ", ") + " where " + fields.Where(x => x.key).SepList(f => "[" + f.name + "] = @" + f.name, f => " and "), table.FullyQualifiedSqlName));
            return result.ToString();
        }

        private void BuildInserts(CCodeClass context)
        {
            Console.WriteLine("Building Insert methods");
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => x.insert))
            {
                var um = context.AddMember(new CCodeMethod("void", string.Format("Insert{0}", table.ClassName)));
                um.Parameters.Add(new CCodeField(table.ClassName, "det"));
                foreach (var sf in table.Field.Where(x => x.specialSpecified))
                {
                    if (sf.special == SpecialField.createdDate || sf.special == SpecialField.updatedDate)
                        um.AddStatement("det.{0} = DateTime.Now;".F(sf.title));
                    if (sf.special == SpecialField.newGuid)
                        um.AddStatement(new CCodeIfStatement("det.{0} == default(Guid)".F(sf.title), new CCodeSnippet("det.{0} = Guid.NewGuid();".F(sf.title))));
                }
                var l = um.AddStatement(new CCodeLockStatment("_syncRoot"));

                MaybeOpenConnection(l);

                string query = InsertCommandText(table);
                l.AddStatement("string query = @\"" + query + "\";");
                var uc = l.AddStatement(new CCodeUsingStatement(string.Format("var command = new {0}(query, _conn)".F(_ado.GetCommandType))));
                if (_ddl.Settings.CommandTimeoutSpecified)
                    uc.AddStatement(new CCodeSnippet("command.CommandTimeout = " + _ddl.Settings.CommandTimeout + ";"));
                if (Procedural)
                    uc.AddStatement(new CCodeSnippet(string.Format("command.CommandType = CommandType.StoredProcedure;")));
                uc.AddStatement(new CCodeIfStatement("_transactionActive", new CCodeSnippet("command.Transaction = _transaction;")));

                SqlParamsWithValue(table.Field, uc, true, true);

                if (_ado is SQLServerADO )
                {
                    if (table.Field.Any(x => x.identity))
                        uc.AddStatement(string.Format("det.{0} = ({1})command.ExecuteScalar();", table.Field.First(x => x.identity).title, table.Field.First(x => x.identity).ClrDataType));
                    else
                        uc.AddStatement(new CCodeSnippet("command.ExecuteNonQuery();"));
                }
                
                if (_ado is SQLCompactADO)
                {
                    uc.AddStatement(new CCodeSnippet("command.ExecuteNonQuery();"));
                    var idcol = table.Field.FirstOrDefault(x => x.identity);
                    if (idcol != null)
                    {
                        var idb = l.AddStatement(new CCodeUsingStatement(string.Format("var command = new {0}(\"Select @@identity as id\", _conn)".F(_ado.GetCommandType))));
                        idb.AddStatement(string.Format("det.{0} = ({1})command.ExecuteScalar();", idcol.title, idcol.ClrDataType));
                    }
                }
                MaybeCloseConnection(l);
            }
        }

        private string InsertCommandText(TableDefinition table)
        {
            if (Procedural)
                return "Insert" + table.ClassName;
            var result = new StringBuilder();
            var fields = table.Field.Where(x => _ado is SQLCompactADO && !x.rowguidcol);
            result.Append(string.Format("insert into {0} (" + fields.SepList(f => "[" + f.name + "]", f => ", ") + ") values (" + fields.SepList(f => "@" + f.ParamName, f => ", ") + ")", table.FullyQualifiedSqlName));
            result.AppendLine();
            if (table.Field.Any(x => x.identity))
            {
                result.Append("go");
                result.Append("select @@identity'identity'");
            }

            return result.ToString().Trim();
        }

        private void BuildSearches(CCodeClass context)
        {
            Console.WriteLine("Building Search methods");
            
            foreach (var table in _ddl.Definition.Schema.Table)
            {
                var searches = new List<Search>();
                
                if (table.Searches != null)
                    searches.AddRange(table.Searches.Search);
                if (!string.IsNullOrEmpty(table.search))
                {
                    string[] patterns = table.search.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                    foreach (var pattern in patterns)
                    {
                        searches.Add(new Search
                        {
                            SearchField = (from n in pattern.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
                                           select new SearchField { fieldname = n }).ToArray()
                        });
                    }
                }

                foreach (var s in searches)
                {
                    var searchFields = new List<Field>();
                    foreach (var sf in s.SearchField)
                    {
                        Field f = table.Field.Where(x => x.name.ToLower() == sf.fieldname.ToLower()).FirstOrDefault();
                        if (f != null)
                            searchFields.Add(f);
                        else
                            throw new Exception(sf.fieldname + " does not exist on table " + table.FullName);
                    }
                    string procArgsName = string.Empty;
                    Array.ForEach(searchFields.ToArray(), x => procArgsName += x.title);
                    string methodName = !string.IsNullOrEmpty( s.name) ? s.name : string.Format("Search{0}By{1}", table.ClassName, procArgsName);
                    string returnType = string.Format("List<{0}>", table.ClassName);
                    var sm = context.AddMember(new CCodeMethod(returnType, methodName));
                    foreach (var f in searchFields)
                        sm.Parameters.Add(new CCodeField(f.ClrDataType, f.ParamName));
                    var l = sm.AddStatement(new CCodeLockStatment("_syncRoot"));
                    l.AddStatement(new CCodeSnippet(string.Format("var result = new List<{0}>();", table.ClassName)));
                    
                    MaybeOpenConnection(l);

                    string query = SearchCommandText(table, searchFields, methodName, s.@operator);
                    l.AddStatement("string query = @\"" + query + "\";");
                    var uc = l.AddStatement(new CCodeUsingStatement("var command = new {0}(query, _conn)".F(_ado.GetCommandType)));

                    if (_ddl.Settings.CommandTimeoutSpecified)
                        uc.AddStatement(new CCodeSnippet(string.Format("command.CommandTimeout = {0};", _ddl.Settings.CommandTimeout)));
                    if (Procedural)
                        uc.AddStatement(new CCodeSnippet(string.Format("command.CommandType = CommandType.StoredProcedure;")));
                    uc.AddStatement(new CCodeIfStatement("_transactionActive", new CCodeSnippet("command.Transaction = _transaction;")));

                    SqlParamsWithValue(searchFields,  uc, false);

                    var ur = uc.AddStatement(new CCodeUsingStatement("IDataReader reader = command.ExecuteReader()"));
                    var w = ur.AddStatement(new CCodeWhileStatement("reader.Read()"));
                    w.AddStatement(new CCodeSnippet(string.Format("var item = new {0}();", table.ClassName)));

                    SqlCastReturnedFields(table.Field.Select(x => new NestedClassFields { Field = x }), false, w);

                    w.AddStatement(new CCodeSnippet("result.Add(item);"));
                    MaybeCloseConnection(l);
                    l.AddStatement(new CCodeSnippet("return result;"));
                }
            }
        }

        private string SearchCommandText(TableDefinition table, IEnumerable<Field> searchFields, string methodName, SearchOperator op)
        {
            if (Procedural)
                return methodName;
            var result = new StringBuilder();
            result.Append(string.Format("select * from {0} where ", table.FullyQualifiedSqlName));
            result.Append(searchFields.SepList(f => "[" + f.name + "] = @" + f.name, f => " " + op.ToString() + " "));
            return result.ToString();
        }

        private void BuildGets(CCodeClass context)
        {
            Console.WriteLine("Building Get methods");
            foreach (var table in _ddl.Definition.Schema.Table.Where(x=> x.get && x.Field.Any(f=>f.key)))
            {
                var pm = context.AddMember(new CCodeMethod(table.ClassName, "Get" + table.ClassName));
                foreach (var f in table.Field.Where(x => x.key))
                        pm.Parameters.Add(new CCodeField(f.ClrDataType, f.ParamName));

                var l = pm.AddStatement(new CCodeLockStatment("_syncRoot"));
                l.AddStatement(new CCodeSnippet(string.Format("var result = new {0}();", table.ClassName)));
                
                MaybeOpenConnection(l);

                string commandText = GetsCommandText(table);
                var uc = l.AddStatement(new CCodeUsingStatement(string.Format("var command = new {0}(\"{1}\", _conn)",_ado.GetCommandType, commandText)));
                if (_ddl.Settings.CommandTimeoutSpecified)
                    uc.AddStatement(new CCodeSnippet("command.CommandTimeout = " + _ddl.Settings.CommandTimeout + ";"));
                if (Procedural)
                    uc.AddStatement(new CCodeSnippet("command.CommandType = CommandType.StoredProcedure;"));
                uc.AddStatement(new CCodeIfStatement("_transactionActive", new CCodeSnippet("command.Transaction = _transaction;")));

                SqlParamsWithValue(table.Field.Where(x=>x.key), uc);

                var ur = uc.AddStatement(new CCodeUsingStatement("IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult)"));
                var w = ur.AddStatement(new CCodeWhileStatement("reader.Read()"));
                SqlCastReturnedFields(table.Field.Select(x => new NestedClassFields { Field = x }), true, w);

                MaybeCloseConnection(l);
                l.AddStatement(new CCodeSnippet("return result;"));

            }
        }

        private string GetsCommandText(TableDefinition table)
        {
            if (Procedural)
                return "Get" + table.name;
            var result = new StringBuilder();
            result.Append(string.Format("select * from {0} where ", table.FullyQualifiedSqlName));
            result.Append(table.Field.Where(x => x.key).SepList(f => "[" + f.name + "] = @" + f.ParamName, f => " and "));
            return result.ToString();
        }

        private void BuildProcs(CCodeClass context)
        {
            Console.WriteLine("Building Proc methods");
            if (_ddl.Definition.Procedures != null)
            {
                foreach (var proc in _ddl.Definition.Procedures)
                {
                    bool hasOutput = (proc.Output != null);
                    string dataType  = string.Empty;
                    string methodReturn = "void";
                    
                    if (hasOutput)
                    {
                        methodReturn = proc.MethodReturn;
                        dataType = proc.MethodReturnDataType;
                        #region old
                        //if (proc.OutputType == ProcedureOutputType.Custom)
                        ////if (proc.Output.type == ProcOutputType.custom)
                        //{
                        //    dataType = proc.name + "Output";
                        //    if (proc.Output.rows == OutputRows.many)
                        //        methodReturn = string.Format("List<{0}>", dataType);
                        //    else
                        //        methodReturn = dataType;
                        //}
                        //else if (proc.Output.type == ProcOutputType.entity)
                        //{
                        //    if (!string.IsNullOrEmpty(proc.Output.basedOnEntity))
                        //        dataType = proc.Output.basedOnEntity;
                        //    else
                        //        throw new Exception("Entity name not supplied with proc:" + proc.name);
                        //    if (proc.Output.rows == OutputRows.many)
                        //        methodReturn = string.Format("List<{0}>", dataType);
                        //    else
                        //        methodReturn = dataType;
                        //}
                        //else if (proc.Output.type == ProcOutputType.table)
                        //{
                        //    methodReturn = "DataTable";
                        //}
                        #endregion
                    }
                    
                    var pm = context.AddMember(new CCodeMethod(methodReturn, proc.ClassName));
                    if (proc.Input != null && proc.Input.Length > 0)
                        foreach (var f in proc.Input)
                            pm.Parameters.Add(new CCodeField(f.ClrDataType, f.ParamName));

                    var l = pm.AddStatement(new CCodeLockStatment("_syncRoot"));
                    if (hasOutput)
                    {
                        l.AddStatement(new CCodeSnippet(string.Format("var result = new {0}();", methodReturn)));
                        if (proc.Output.rows == OutputRows.single)
                        {
                            foreach (var g in proc.NestedFields.GroupBy(x => x, x => x, new NestedFieldComparer()).Where(x => x.Key.NestedClassName != null))
                                l.AddStatement(new CCodeSnippet("result.{0} = new {1}();".F(g.Key.NestedClassName, g.Key.NestedType)));
                        }
                    }
                    
                    MaybeOpenConnection(l);
                    string cmdtxt = ProcsCommandText(proc);
                    var uc = l.AddStatement(new CCodeUsingStatement(string.Format("var command = new {0}(@\"{1}\", _conn)", _ado.GetCommandType, cmdtxt)));
                    if (_ddl.Settings.CommandTimeoutSpecified)
                        uc.AddStatement(new CCodeSnippet("command.CommandTimeout = " + _ddl.Settings.CommandTimeout + ";"));
                    if (proc.Origin == Origin.SQL)
                        uc.AddStatement(new CCodeSnippet("command.CommandType = CommandType.StoredProcedure;"));
                    uc.AddStatement(new CCodeIfStatement("_transactionActive", new CCodeSnippet("command.Transaction = _transaction;")));
                    if (proc.Input != null && proc.Input.Length > 0)
                        SqlParamsWithValue(proc.Input.ToList(), uc);
                    if (hasOutput)
                    {
                        if (proc.Output.type == ProcDataOutputType.dataTable)
                            uc.AddStatement("result.Load(command.ExecuteReader());");
                        else
                        {
                            var ur = uc.AddStatement(new CCodeUsingStatement("IDataReader reader = command.ExecuteReader()"));
                            var w = ur.AddStatement(new CCodeWhileStatement("reader.Read()"));
                            if (proc.Output.rows == OutputRows.many)
                            {
                                w.AddStatement(new CCodeSnippet(string.Format("var item = new {0}();", dataType)));
                                foreach (var g in proc.NestedFields.GroupBy(x => x, x => x, new NestedFieldComparer()).Where(x => x.Key.NestedClassName != null))
                                    w.AddStatement(new CCodeSnippet("item.{0} = new {1}();".F(g.Key.NestedClassName, g.Key.NestedType)));
                            }
                            SqlCastReturnedFields(proc.NestedFields, proc.Output.rows== OutputRows.single, w);
                            if (proc.Output.rows == OutputRows.many)
                                w.AddStatement(new CCodeSnippet("result.Add(item);"));
                        }
                    }
                    else
                        uc.AddStatement(new CCodeSnippet("command.ExecuteNonQuery();"));
                    MaybeCloseConnection(l);
                    if (hasOutput)
                    {

                        l.AddStatement(new CCodeSnippet("return result;"));
                    }
                }
            }
        }

        private string ProcsCommandText(Procedure proc)
        {
            if (proc.Origin == Origin.SQL)
                return proc.FullyQualifiedSqlName;
            return proc.Sql.Trim();            
        }

        //private static void SqlParameters(IEnumerable<Field> fields, bool isParam, CCodeStatement block)
        //{
        //    foreach (var field in fields)
        //    {
        //        string paramValue = (!isParam) ? string.Format("det.{0}", field.title) : string.Format("{0}", field.name);
        //        if (field.datatype == FieldDataType.@string)
        //            block.AddStatement(new CCodeSnippet(string.Format("SqlParameter {0}param = command.Parameters.Add(\"@{0}\", {1}, {2});", field.name, DataTypeConversion.GetSqlDbType(field), field.len)));
        //        else
        //            block.AddStatement(new CCodeSnippet(string.Format("SqlParameter {0}param = command.Parameters.Add(\"@{0}\", {1});", field.name, DataTypeConversion.GetSqlDbType(field))));
        //        if (field.nulls || field.datatype == FieldDataType.@string)
        //        {
        //            if (DataTypeConversion.IsNullableType(field))
        //                block.AddStatement(new CCodeSnippet(string.Format("if ({0}.HasValue) {1}param.Value = {0}.Value;", paramValue, field.name)));
        //            else
        //                block.AddStatement(new CCodeSnippet(string.Format("if ({0}!=null) {1}param.Value = {0};", paramValue, field.name)));
        //            block.AddStatement(new CCodeSnippet(string.Format("else {0}param.Value = DBNull.Value;", field.name)));
        //        }
        //        else
        //            block.AddStatement(new CCodeSnippet(string.Format("{0}param.Value = {1};", field.name, paramValue)));
        //    }
        //}

        private void SqlParamsWithValue(IEnumerable<Field> fields, CCodeStatement block, bool isParam = false, bool isInsert = false)
        {
            var useFields = fields;
            if (isInsert)
                useFields = fields.Where(x => _ado is SQLCompactADO && !x.rowguidcol);
            foreach (var f in useFields)
            {
                string paramValue = (isParam) ? string.Format("det.{0}", f.title) : f.ParamName;
                if (f.nulls)
                {
                    if (!DataTypeConversion.IsNullableType(f))
                    {
                        var iff = block.AddStatement(new CCodeIfStatement(string.Format("{0}.HasValue", paramValue), new CCodeSnippet("command.Parameters.AddWithValue(\"@" + f.ParamName + "\", " + paramValue + ");")));
                        iff.ElseStatements.Add(new CCodeSnippet("command.Parameters.AddWithValue(\"@" + f.ParamName + "\", DBNull.Value);"));
                    }
                    else
                    {
                        var iff = block.AddStatement(new CCodeIfStatement(string.Format("{0} != null", paramValue), new CCodeSnippet("command.Parameters.AddWithValue(\"@" + f.ParamName + "\", " + paramValue + ");")));
                        iff.ElseStatements.Add(new CCodeSnippet("command.Parameters.AddWithValue(\"@" + f.ParamName + "\", DBNull.Value);"));
                    }

                }
                else
                {
                    if (f.ClrDataType == "string")
                    {
                        var iff = block.AddStatement(new CCodeIfStatement(string.Format("{0} != null", paramValue), new CCodeSnippet("command.Parameters.AddWithValue(\"@" + f.ParamName + "\", " + paramValue + ");")));
                        iff.ElseStatements.Add(new CCodeSnippet(string.Format("throw new DataException(\"{0} cannot be null.\");", f.name)));
                    }
                    else
                        block.AddStatement("command.Parameters.AddWithValue(\"@" + f.ParamName + "\", " + paramValue + ");");
                }
            }
        }

        private void BuildAlls(CCodeClass context)
        {
            Console.WriteLine("Building All methods");
            foreach (var table in _ddl.Definition.Schema.Table.Where(x => x.all))
            {
                string methodName = string.Format("All{0}", table.ClassName);
                var am = new CCodeMethod("List<" + table.ClassName + ">", methodName);
                //if (!Procedural)
                //    am.Parameters.Add(new CCodeField("IEnumerable<string>", "wExpressions") { Initializer = new CCodeSnippet("null") });
                context.AddStatement(am);
                var l = new CCodeLockStatment("_syncRoot");
                am.AddStatement(l);
                l.AddStatement(new CCodeSnippet(string.Format("var result = new List<{0}>();", table.ClassName)));
                MaybeOpenConnection(l);

                string query = AllCommand(table);
                l.AddStatement("const string query = @\"" + query + "\";");

                var u = new CCodeUsingStatement("var command = new {0}(query, _conn)".F(_ado.GetCommandType));
                l.AddStatement(u);
                if (_ddl.Settings.CommandTimeoutSpecified)
                    u.AddStatement(new CCodeSnippet("command.CommandTimeout = " + _ddl.Settings.CommandTimeout + ";"));
                if (Procedural)
                    u.AddStatement(new CCodeSnippet("command.CommandType = CommandType.StoredProcedure;"));
                u.AddStatement(new CCodeIfStatement("_transactionActive", new CCodeSnippet("command.Transaction = _transaction;")));
                var ureader = new CCodeUsingStatement("IDataReader reader = command.ExecuteReader()");
                u.AddStatement(ureader);
                var ws = new CCodeWhileStatement("reader.Read()");
                ureader.AddStatement(ws);
                ws.AddStatement(new CCodeSnippet(string.Format("var item = new {0}();", table.ClassName)));
                //
                SqlCastReturnedFields(table.Field.Select(x=> new NestedClassFields {Field = x}), false, ws);
                //
                ws.AddStatement(new CCodeSnippet("result.Add(item);"));
                MaybeCloseConnection(l);
                l.AddStatement(new CCodeSnippet("return result;"));
                
            }
        }

        private string AllCommand(TableDefinition table)
        {
            if (Procedural)
                return "All" + table.name;
            var result = new StringBuilder();
            result.Append(string.Format("select * from {0}", table.FullyQualifiedSqlName));
            
            return result.ToString();
        }

        private static void SqlCastReturnedFields(IEnumerable<NestedClassFields> fields, bool singleRow, CCodeStatement block)
        {
            DataReaderGetMethod(fields, singleRow, block);
            return; 

            //string item = (singleRow) ? "result" : "item";
            //foreach (var field in fields)
            //{
            //    string isdbNull = string.Empty;
            //    string notnull;
            //    if (!string.IsNullOrEmpty(field.EnumName))
            //        notnull = string.Format("({0})({1})reader[\"{2}\"]", field.ClrDataType, field.UnderlyingEnumType, field.SqlFieldName);
            //    else
            //        notnull = string.Format("({0})reader[\"{1}\"]", field.ClrDataType, field.SqlFieldName);
            //    string isnull = string.Empty;
            //    if (field.nulls)
            //    {
            //        isdbNull = string.Format("(reader[\"{0}\"] != DBNull.Value)?", field.SqlFieldName);
            //        isnull = ":null";
            //    }
            //    block.AddStatement(new CCodeSnippet(string.Format("{4}.{0} = {1}{2}{3};", field.title, isdbNull, notnull, isnull, item)));
            //}
        }

        public static void DataReaderGetMethod(IEnumerable<NestedClassFields> fields, bool singleRow, CCodeStatement block)
        {
            int count=0;
            string item = (singleRow) ? "result" : "item";
            foreach (var field in fields)
            {
                var f = field.Field;
                string nested = string.IsNullOrEmpty(field.NestedClassName) ? "" : "." + field.NestedClassName;
                string readerMethoad;
                if (f.SqlDataType == "time")
                    readerMethoad = "((SqlDataReader)reader)." + DataTypeConversion.GetDataReaderGetMethod(f).F(count);
                else if (f.SqlDataType == "varbinary")
                    readerMethoad = string.Format("({0})reader[\"{1}\"]", f.ClrDataType, f.SqlFieldName);
                else if(f.SqlDataType == "image")
                    readerMethoad = string.Format("({0})reader[\"{1}\"]", f.ClrDataType, f.SqlFieldName);
                else
                    readerMethoad = "reader." + DataTypeConversion.GetDataReaderGetMethod(f).F(count);

                if (!string.IsNullOrEmpty(f.EnumName))
                    readerMethoad = "({0})".F(f.ClrDataType) + readerMethoad;

                if (f.nulls)
                {
                    var i = block.AddStatement(new CCodeIfStatement(string.Format("reader.IsDBNull({0})", count), new CCodeSnippet("{0}{1}.{2} = null;".F(item, nested, f.title))));
                    i.ElseStatements.Add(new CCodeSnippet(string.Format("{0}{1}.{2} = {3};", item, nested, f.title, readerMethoad)));
                }
                else
                    block.AddStatement(new CCodeSnippet(string.Format("{0}{1}.{2} = {3};", item, nested, f.title, readerMethoad)));
                ++count;
            }
        }

        private static void MaybeOpenConnection(CCodeStatement block)
        {
            block.AddStatement(new CCodeIfStatement("_conn.State != ConnectionState.Open", new CCodeSnippet("_conn.Open();")));
        }

        private static void MaybeCloseConnection(CCodeStatement block)
        {
            block.AddStatement(new CCodeIfStatement("!KeepAlive", new CCodeSnippet("_conn.Close();")));
        }

        private static void BuildTransactions(CCodeClass context)
        {
            var btm = context.AddMember(new CCodeMethod("bool", "BeginTransaction"));
            btm.AddStatement(new CCodeIfStatement("!_transactionActive", new CCodeSnippet("_keepAlive = true;"), new CCodeSnippet("_transactionActive = true;"), new CCodeIfStatement("_conn.State != ConnectionState.Open", new CCodeSnippet("_conn.Open();")), new CCodeSnippet("_transaction = _conn.BeginTransaction();"), new CCodeSnippet("return true;")));
            btm.AddStatement(new CCodeSnippet("return true;"));

            var ctm = context.AddMember(new CCodeMethod("void", "CommitTransaction"));
            ctm.AddStatement(new CCodeIfStatement("_transactionActive", new CCodeSnippet("_transaction.Commit();"), new CCodeSnippet("_transactionActive = false;"), new CCodeSnippet("_transaction.Dispose();")));

            var rtm = context.AddMember(new CCodeMethod("void", "RollbackTransaction"));
            rtm.AddStatement(new CCodeIfStatement("_transactionActive", new CCodeSnippet("_transaction.Rollback();"), new CCodeSnippet("_transactionActive = false;"), new CCodeSnippet("_transaction.Dispose();")));
        }

        private void BuildExecuteSqlMethod(CCodeClass context)
        {
            var esm = context.AddMember(new CCodeMethod("DataTable", "ExecuteSql", new CCodeField("string", "sql")));
            var ls = esm.AddStatement(new CCodeLockStatment("_syncRoot"));
            ls.AddStatement(new CCodeSnippet("var result = new DataTable(\"ExecuteSql\");"));
            ls.AddStatement(new CCodeIfStatement("_conn.State != ConnectionState.Open", new CCodeSnippet("_conn.Open();")));

            var us = ls.AddStatement(new CCodeUsingStatement("var command = new {0}(sql, _conn)".F(_ado.GetCommandType)));
            if (_ddl.Settings.CommandTimeoutSpecified)
                us.AddStatement(new CCodeSnippet("command.CommandTimeout = " + _ddl.Settings.CommandTimeout + ";"));
            us.AddStatement(new CCodeIfStatement("_transactionActive", new CCodeSnippet("command.Transaction = _transaction;")));
            us.AddStatement(new CCodeUsingStatement("IDataReader reader = command.ExecuteReader()", new CCodeSnippet("result.Load(reader);")));
            ls.AddStatement(new CCodeIfStatement("!_keepAlive", new CCodeSnippet("_conn.Close();")));
            ls.AddStatement(new CCodeSnippet("return result;"));
        }

        private static void BuildDisposeMethod(CCodeClass context)
        {
            var dm = context.AddMember(new CCodeMethod("void", "Dispose"));
            dm.AddStatement(new CCodeIfStatement("_transactionActive", new CCodeSnippet("RollbackTransaction();"), new CCodeSnippet("_transactionActive = false;")));
            dm.AddStatement(new CCodeIfStatement("_conn.State == ConnectionState.Open", new CCodeSnippet("_conn.Close();")));
            dm.AddStatement(new CCodeSnippet("_conn.Dispose();"));
            dm.AddStatement(new CCodeSnippet("_conn = null;"));
            dm.AddStatement(new CCodeSnippet("GC.SuppressFinalize(this);"));
        }
    }

    public class NestedFieldComparer : IEqualityComparer<NestedClassFields>
    {
        public bool Equals(NestedClassFields x, NestedClassFields y)
        {
            return x.NestedClassName == y.NestedClassName && x.NestedType == y.NestedType;
        }

        public int GetHashCode(NestedClassFields obj)
        {
            return (!string.IsNullOrEmpty(obj.NestedType) ? obj.NestedType.GetHashCode() : 0) + 
                (!string.IsNullOrEmpty(obj.NestedClassName) ? obj.NestedClassName.GetHashCode() : 0);
        }
    }
}
